package com.ruowei.modules.foodOrder.repository;

import com.ruowei.common.repository.BaseRepository;
import com.ruowei.modules.foodOrder.domain.Order;

import com.ruowei.modules.foodOrder.domain.QOrder;
import com.ruowei.modules.foodOrder.domain.enumeration.OrderStatus;
import com.ruowei.modules.foodOrder.web.dto.MerchandiseSortSqlDTO;
import com.ruowei.modules.foodOrder.web.dto.StatisticalMouthDTO;
import com.ruowei.modules.foodOrder.web.dto.StatisticalYearDTO;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * Spring Data  repository for the Order entity.
 */
@Repository
public interface OrderRepository extends BaseRepository<Long, Order, QOrder> {

    Optional<Order> findOneByOrderCodeAndStatusAndDeleteStatus(String orderCode, OrderStatus status, Boolean deleteStatus);

    Optional<Order> findByOrderCode(String orderCode);

    Optional<Order> findOneByOrderCodeAndDeleteStatus(String orderCode, Boolean deleteStatus);

    Optional<List<Order>> findAllByMemberCodeAndStatus(String memberCode, OrderStatus status);

    List<Order> findAllByCourierCodeAndStatus(String courierCode, OrderStatus status);

    @Query(value = "select localDate," +
        "       merchant.merchant_name                 as merchantName," +
        "       ifnull(sum(jhi_order.total_amount), 0) as amount," +
        "       count(jhi_order.order_code)            as count" +
        " from ( select ADDDATE(y.first, x.no - 1) as localDate" +
        "       from (" +
        "               select 1 as no" +
        "               union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7" +
        "               union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13" +
        "               union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19" +
        "               union all select 20 union all select 21 union all select 22 union all select 23 union all select 24 union all select 25" +
        "               union all select 26 union all select 27 union all select 28 union all select 29 union all select 30 union all select 31" +
        "           ) x," +
        "           (select CURDATE() - interval day(CURDATE()) - 1 day as first, day(CURDATE()) - 1 as yestoday) y" +
        "      where x.no <= y.yestoday) as d" +
        "         left join merchant on true" +
        "         left join jhi_order on merchant.merchant_code = jhi_order.merchant_code and jhi_order.status = 'DELIVERED' and" +
        "                                localDate = jhi_order.create_date" +
        " group by localDate, merchant.merchant_name" +
        " order by localDate;"
        , nativeQuery = true)
    List<StatisticalMouthDTO> getOrderVolumeAndAmountForMouth();

    @Query(value = "select x.month, count(jhi_order.total_amount) as count, ifnull(sum(jhi_order.total_amount), 0) as amount" +
        " from (" +
        "         select 1 as month union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 " +
        "         union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) x" +
        "         left join jhi_order on x.month = month(jhi_order.create_date) and jhi_order.status = 'DELIVERED'" +
        " group by x.month" +
        " order by x.month;", nativeQuery = true)
    List<StatisticalYearDTO> getOrderVolumeAndAmountForYear();

    @Query(value = "select merchandise.merchandise_name as merchandiseName, merchant.merchant_name as merchantName, count(x.id) as orderCount, ifnull(sum(x.quantity) ,0.00) as saleCount, ifnull(sum(x.quantity * x.unit_price), 0.00) as amount" +
        " from merchandise" +
        " left join merchant on merchant.merchant_code = merchandise.merchant_code" +
        " left join ( select jhi_order.id, order_item.merchandise_code, order_item.quantity, order_item.unit_price" +
        "    from jhi_order" +
        "    left join order_item on jhi_order.order_code = order_item.order_code and jhi_order.status = 'DELIVERED') x on merchandise.merchandise_code = x.merchandise_code" +
        " group by merchant.merchant_code, merchant.merchant_name, merchandise.merchandise_code, merchandise.merchandise_name", nativeQuery = true)
    List<MerchandiseSortSqlDTO> getMerchandiseSort();
}
